package db;

import java.io.DataOutputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import core.Dictionary;

public class DbDictionaries {
    public static Logger LOG = Logger.getLogger("db.DbDictionaries");
    private static List<Dictionary> dictList = new ArrayList<Dictionary>();
    // load all dictionaries from database to the dictList.
    public DbDictionaries () {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select * from m_dictionaries");
            rs = pstat.executeQuery();
            while (rs.next()) {
                Dictionary dt = new Dictionary( rs.getString("m_dictURL"),rs.getInt("m_ID"), rs.getInt("m_fromID"),
                							   rs.getInt("m_toID"), rs.getInt("m_delCount"));
                dictList.add(dt);
            }
        } catch (Exception ex) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
    }
    // get an dictionary by its id
    public Dictionary getDictionary(int dictID) {
        Dictionary dict = null;
        for (int i = dictList.size(); i > 0;) {
            --i;
            dict = dictList.get(i);
            if (dict.getDictID()== dictID) {
                return dict;
            }
        }
        return null;
    }
    // get an dictionary by its fromID and toID of language
    public Dictionary getDictionary(int fromID, int toID) {
        Dictionary dict = null;
        for (int i = dictList.size(); i > 0;) {
            --i;
            dict = dictList.get(i);
            if (dict.getFromID()== fromID && dict.getToID() == toID) {
                return dict;
            }
        }
        return null;
    }
    
    // add new a dictionary
    public boolean addNewDictionary(int fromID, int toID, String dictURL) {

        // Warning: Could not to add new dictionary English-English
        Dictionary dt;
        for (int i = dictList.size(); i > 0;) {
            --i;
            dt = dictList.get(i);
            if (dt.getFromID() == fromID && dt.getToID() == toID) {
                return false;
            }
        }
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;

        // get dictionaries from data base and add to the list of dictionaries
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("insert into m_dictionaries(m_fromID,m_toID,m_dictUrl) values(" + fromID + ","
                    + toID + ",N'" + dictURL + "')");
            if (pstat.executeUpdate() == 1) {
                pstat.close();
                pstat = cnn.prepareStatement("select m_id,m_delCount from m_dictionaries where m_fromID=" + fromID
                        + " and m_toID=" + toID);
                rs = pstat.executeQuery();
                if (rs.next()) {
                    Dictionary dict = new Dictionary(dictURL, rs.getInt("m_id"), fromID, toID, rs.getInt("m_delCount"));
                    dictList.add(dict);
                    bOK = true;
                }
            }
        } catch (Exception ex) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }

        return bOK;
    }

    // delete a dictionary
    public boolean deleteDict(int dictID) {
        boolean bOK = false;
        Connection cnn = null;
        // delete from m_approvings table
        PreparedStatement pstatDelApproving = null;
        // delete from m_approveds table
        PreparedStatement pstatDelApproved = null;
        // delete from m_meanings table
        PreparedStatement pstatDelMeaning = null;
        // delete from m_managerindict table
        PreparedStatement pstatDelManager = null;
        // delete from m_words table
        PreparedStatement pstatDelWord = null;
        // delete from m_dictionaries table
        PreparedStatement pstatDelDict = null;
        try {
            cnn = DbController.getConnection();
            pstatDelApproving = cnn
                    .prepareStatement("delete from m_approvings where m_wordID in (select m_id from m_words where m_dictID="
                            + dictID + ")");
            pstatDelApproved = cnn.prepareStatement("delete from m_approveds where m_dictID=" + dictID);
            pstatDelMeaning = cnn
                    .prepareStatement("delete from m_meanings where m_wordID in (select m_id from m_words where m_dictID="
                            + dictID + ")");
            pstatDelManager = cnn.prepareStatement("delete from m_managerindict where m_dictID=" + dictID);
            pstatDelWord = cnn.prepareStatement("delete from m_words where m_dictID=" + dictID);
            pstatDelDict = cnn.prepareStatement("delete from m_dictionaries where m_id=" + dictID);
            for (int i = dictList.size(); i > 0;) {
                --i;
                // check if dictionary exists in the list
                if (dictList.get(i).getDictID() == dictID) {
                    try {
                        pstatDelApproving.executeUpdate();
                        bOK = true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelApproving.close();
                    }
                    try {
                        pstatDelApproved.executeUpdate();
                        bOK &= true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelApproved.close();
                    }
                    try {
                        pstatDelMeaning.executeUpdate();
                        bOK &= true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelMeaning.close();
                    }
                    try {
                        pstatDelManager.executeUpdate();
                        bOK &= true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelManager.close();
                    }
                    try {
                        pstatDelWord.executeUpdate();
                        bOK &= true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelWord.close();
                    }
                    try {
                        pstatDelDict.executeUpdate();
                        bOK &= true;
                    } catch (Exception ex) {
                        bOK = false;
                    } finally {
                        pstatDelDict.close();
                    }
                    // remove dictionary from the list
                    dictList.remove(i);
                    break;
                }
            }
        } catch (Exception ex) {
            bOK = false;
        } finally {
            try {
                if (pstatDelApproving != null) {
                    pstatDelApproving.close();
                }
                if (pstatDelApproved != null) {
                    pstatDelApproved.close();
                }
                if (pstatDelMeaning != null) {
                    pstatDelMeaning.close();
                }
                if (pstatDelManager != null) {
                    pstatDelManager.close();
                }
                if (pstatDelWord != null) {
                    pstatDelWord.close();
                }
                if (pstatDelDict != null) {
                    pstatDelDict.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }
    // create new dictionary
    public boolean createNewDictionary(int fromID, int toID, String bookname, String fileName, String folderName) {
        boolean bOK = false;

        if (this.getDictionary(fromID, toID) != null) {
            return false;
        }
        (new java.io.File(DbController.getDictRootFolder() + "/" + folderName)).mkdir();
        String url = DbController.getDictRootFolder() + "/" + folderName + "/" + fileName;
        String dictFile = url + ".dict";
        String idxFile = url + ".idx";
        String ifoFile = url + ".ifo";

        if ((new java.io.File(dictFile)).exists() || (new java.io.File(idxFile)).exists()
                || (new java.io.File(ifoFile)).exists()) {
            return false;
        }

        try {
            DataOutputStream dt = new DataOutputStream(new FileOutputStream(dictFile));
            // java.io.FileWriter fw = new java.io.FileWriter(dictFile);
            String strFirstWord = "@00-database-info\nThis is the " + bookname
                    + " dictionary database of the Free Vietnamese Dic";
            dt.write(strFirstWord.getBytes());
            dt.flush();
            dt.close();

            dt = new DataOutputStream(new FileOutputStream(idxFile));
            dt.write('\0');
            dt.write(convertAnInt32(0));
            dt.write(convertAnInt32(strFirstWord.length()));
            dt.flush();
            dt.close();
            java.io.FileWriter fw = new java.io.FileWriter(ifoFile);
            fw.write("StarDict's dict ifo file\n");
            fw.write("version=2.4.2\n");
            fw.write("wordcount=1\n");
            fw.write("idxfilesize=9\n");
            fw.write("bookname=" + bookname + "\n");
            fw.write("author=G3-K9\n");
            fw.write("website=http://raiportal.fsofthcmworks.com\n");
            fw.write("description=convert to StarDict by FPT Software\n");
            fw.write("date=2005.10.09\n");
            fw.write("sametypesequence=m\n");
            fw.flush();
            fw.close();

            // add to database and the list
            if (this.addNewDictionary(fromID, toID, url)) {
                bOK = true;
            } else {
                (new java.io.File(dictFile)).delete();
                (new java.io.File(idxFile)).delete();
                (new java.io.File(ifoFile)).delete();
            }
        } catch (Exception ex) {
            bOK = false;
        }

        return bOK;
    }

    public byte[] convertAnInt32(int val) {
        byte[] str = new byte[4];
        str[0] = (byte) ((val & 0xFF000000) >> 24);
        str[1] = (byte) ((val & 0x00FF0000) >> 16);
        str[2] = (byte) ((val & 0x0000FF00) >> 8);
        str[3] = (byte) ((val & 0x000000FF));
        return str;
    }

    public void updateDelCount(int dictID, int delCount) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("update m_dictionaries set m_delCount=" + delCount + " where m_id =" + dictID);
            pstat.executeUpdate();
        } catch (Exception ex) {
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
    }

    public String getDictListHTML() {
        String list = "<table><tr><td style=\"width:70px\">Dictionary: </td><td>";
        list += "<select name=slDict style=\"width: 150px\">";
        for (int i = 0; i < dictList.size(); i++) {
            Dictionary dict = dictList.get(i);
            list += "<option value=" + dict.getDictID() + ">" + dict.getIfoFile().getStrBookname() + "</option>";
        }
        list += "</select>";
        list += "</td></tr>";
        list += "</table>";
        return list;
    }
}